/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package Controller;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */


/**
 *
 * @author CCD gruppe 2
 */
import java.sql.*;
import java.util.ArrayList;
import Module.*;


public class ResourceController {
    /**
     * Object used to connect to the database
     */
    private Database database = new Database();
    /**
     * Method used to register a resource, also register a resourceType
     * returns [0] resourceTypeId [1] resourceId
     * @param name
     * @param resourceTypeName
     * @param resourceTypeId
     * @return
     */
    public int[] registerResource(String name, String resourceTypeName, int resourceTypeId){
        int[] returvalue= new int[2];
        Connection conn = null;
        
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement(
                    "Insert into resource values(DEFAULT,?,?)");
            statement.setString(1, name);

            if(resourceTypeId>0){
                returvalue[0] = resourceTypeId;
                statement.setInt(2, resourceTypeId);
            }
            //Need to find the id to this resourceTypeName
            else{
                PreparedStatement s = conn.prepareStatement("select resourceTypeId from resourceType where resourceType=?");
                s.setString(1, resourceTypeName);
                ResultSet rs = s.executeQuery();
                int id=0;
                while(rs.next()){
                    id = rs.getInt(1);
                    returvalue[0] = id;

                }
                statement.setInt(2, id);
                rs.close();
            }
            statement.executeUpdate();
            statement = conn.prepareStatement("select * from resource");
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                returvalue[1]=rs.getInt(1);
            }
            conn.close();
            rs.close();
            return returvalue;

        }catch (Exception e) {
            System.err.println(e);

        }
        return returvalue;
    }
    /**
     * Method used to register a resourceType
     * @param resourceName
     * @return
     */
    public int registerResourceType(String resourceName){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            //String[] autogen = {"id"};
            PreparedStatement statement = conn.prepareStatement(
                    "Insert into resourceType values(DEFAULT,?)");
            statement.setString(1, resourceName);
            statement.executeUpdate();
            statement = conn.prepareStatement("select * from resourceType order by resourceTypeId desc");
            ResultSet rs = statement.executeQuery();
            int id = 0;
            while(rs.next()){
                id = rs.getInt(1);
            }
            conn.close();
            rs.close();
            return id;
        }catch (Exception e) {
            System.err.println(e);

        }
        return 0;
    }
    /**
     * Method used to gett all resourceTypes
     * @return
     */
    public ArrayList<ResourceType> getAllResourceType(){
        Connection conn = null;
        ArrayList<ResourceType> resources = new ArrayList<ResourceType>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("select * from resourceType");
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                resources.add(new ResourceType(rs.getInt(1),rs.getString(2)));
            }
            conn.close();
            return resources;

        }catch(Exception e){
            System.err.println(e);
        }

        return resources;

    }
    /**
     * Method used to edit a resource
     * @param resource
     * @return
     */
    public boolean editResource(Resource resource){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("Update resource "
                    + "set resourceName=?, resourceTypeID=? where resourceId =?");
            statement.setString(1, resource.getName());
            statement.setInt(2, resource.getResourceTypeId());
            statement.setInt(3, resource.getId());
            statement.executeUpdate();
            conn.close();
            return true;
        }catch(Exception e){
            System.err.println(e);
        }

        return false;
    }
    /**
     * Method used to get all resources
     * @return
     */
    public ArrayList<Resource> getAllResources(){
        Connection conn = null;
        ArrayList<Resource> resources = new ArrayList<Resource>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("select * from resource");
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                resources.add(new Resource(rs.getInt(1), rs.getString(2),rs.getInt(3)));
            }
            conn.close();
            return resources;

        }catch(Exception e){
            System.err.println(e);
        }

        return resources;

    }
    /**
     * method userd to get all resource to the project
     * @param projectId
     * @return
     */
    public ArrayList<ResourceProject> getResourcesToProject(int projectId){
        Connection conn = null;
        ArrayList<ResourceProject> resources = new ArrayList<ResourceProject>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement(""
                        + "select * from resourceProject left join resource on(resourceProject.resourceId = resource.resourceId) "
                        + " where resourceProject.projectId=?");
             statement.setInt(1, projectId);
             ResultSet rs = statement.executeQuery();
             while(rs.next()){
                 resources.add(new ResourceProject(new Resource(rs.getInt(5),rs.getString(6), rs.getInt(7)),rs.getString(3),
                         rs.getString(4),rs.getInt(2)));
             }
             conn.close();
             return resources;
        }catch(Exception e){
            System.err.println(e);
        }
        return resources;
    }

    /**
     * Method used to delete all the resources to a porject
     * @param projectId
     * @return
     */
    public boolean deleteResourcesToProject(int projectId){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("delete from resourceProject where projectId = ?");
            statement.setInt(1, projectId);
        }catch(Exception e){
            System.err.println(e);
        }
        return false;
    }
    /**
     * Method used to delete a singel resource to a project
     * @param projectId
     * @param resourceId
     */

    public ArrayList<String> getAllSessionPlaces(){
       Connection conn = null;
       ArrayList<String> places = new ArrayList<String>();
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("select * from resource where resourceTypeId=2");
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                places.add(rs.getString(2));
            }
            rs.close();
            rs.close();
        }catch(Exception e){
            System.err.println(e);
        }
        return places;
    }

    /**
     * Method used to delete a resurce to a project. 
     * @param projectId
     * @param resourceId
     */

    public void deleteResourceToProject(int projectId, int resourceId){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement statement = conn.prepareStatement("delete from resourceProject where projectId=? and resourceId=?");
            statement.setInt(1, projectId);
            statement.setInt(2, resourceId);
            statement.executeUpdate();
            conn.close();
        }catch(Exception e){
            System.err.println(e);
        }
    }
    /**
     * Method used to register a resource to a project
     * @param resources
     * @return
     */
    public String registerResourceToProject(ResourceProject resources){
        Connection conn =null;
        try{
            String text = checkDates(resources.getResource().getId(), resources.getFromdate(), resources.getToDate());
            if(text.length()<5){
                conn = DriverManager.getConnection(database.getURL());
                PreparedStatement statement = conn.prepareStatement("insert into resourceProject values(?,?,?,?)");
                statement.setInt(1, resources.getResource().getId());
                statement.setInt(2, resources.getProjectId());
                statement.setString(3, resources.getFromdate());
                statement.setString(4, resources.getToDate());
                statement.executeUpdate();
                conn.close();
                return text;
            }
            return text;

        }catch(Exception e){
            System.err.println(e);
        }
        return "";
    }
    /**
     * Private method used to check if the selected date interval is valid
     * If another project already uses this resource a error message is returned
     * @param resourceId
     * @param fromDate
     * @param toDate
     * @return
     */
    private String checkDates(int resourceId, String fromDate, String toDate){
        Connection conn = null;
        try{
            conn = DriverManager.getConnection(database.getURL());
            PreparedStatement s = conn.prepareStatement("select * from resourceProject where resourceId=?");
            s.setInt(1, resourceId);
            int from = Integer.parseInt(fromDate.replaceAll("-", ""));
            int to = Integer.parseInt(toDate.replaceAll("-", ""));
            ResultSet rs = s.executeQuery();
            while(rs.next()){
                String databaseFrom = rs.getString(3);
                String databaseTo = rs.getString(4);
                int dfrom = Integer.parseInt(databaseFrom.replaceAll("-",""));
                int dto = Integer.parseInt(databaseTo.replaceAll("-",""));
                if(from>=dfrom && from<=dto) {
                    conn.close();
                    return "resurs er opptatt fra " + databaseFrom + " til " + databaseTo;
                    }
                if(to>=dfrom && to<=dto){
                    conn.close();
                    return "resurs er opptatt fra " + databaseFrom + " til " + databaseTo;
                }
            }
        }catch(Exception e ){
            System.err.println(e);
        }
        return "";

    }


}

